﻿using Microsoft.EntityFrameworkCore;
using System.Threading.Tasks;

namespace 原生SQL
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            /*
            string WareName = "瓜子";
            double Price = 19;
            int WareQty = 10;
            */
            

            using (var db = new NetTest2Context())
            {
                /*
                //非查询原生SQL
                await db.Database.ExecuteSqlInterpolatedAsync(
                    $@"
                        insert T_Sales(WareName,Price,WareQty)
                        values({WareName},{Price},{WareQty});
                        "
                    );
                */

                //与实体相关的查询原生SQL

                //一
                string WareName = "瓜子"; //或者string WareName = "%瓜子%"，然后下面的likePattern就直接用WareName
                string likePattern = $"%{WareName}%";
                var sales = db.TSales
                    .FromSqlInterpolated(
                        $@"
                        select * from T_Sales
                        where WareName like {likePattern}
                        "
                        ).ToList();
                   

                foreach (var sale in sales.OrderBy(x=>Guid.NewGuid()).Skip(1).Take(1))//随机排序
                {
                   
                    Console.WriteLine($"Id:{sale.Id}, WareName:{sale.WareName}, Price:{sale.Price}, WareQty:{sale.WareQty}");
                }


                //二
                string titleKeyword = "%震惊%";
                var articles = db.TArticles.FromSqlInterpolated(@$"select * from T_Articles where Title like {titleKeyword}");
                foreach (var article in articles.Include(x => x.TComments))
                {
                    Console.WriteLine($"Id:{article.Id}, Title:{article.Title}");
                    foreach (var comment in article.TComments)
                    {
                        Console.WriteLine($"\t CommentId:{comment.Id}, Content:{comment.Message}");
                    }
                }


                //任意SQL查询：待验证
                //还有复杂SQL怎么办？ 
                //Dapper？
                //复杂报表怎么办？EF CORE不是很适合复杂报表查询，建议使用专门的报表工具，或者直接用Dapper等微型ORM来处理复杂报表查询需求。
                //var sales2 = await db.TSales
                //    .FromSqlRaw("select * from T_Sales where Price>@price",
                //    new Microsoft.Data.SqlClient.SqlParameter("@price", 20))
                //    .ToListAsync();

                //db.Database.SqlQueryAsync<decimal>(
                //    "select sum(Price*WareQty) from T_Sales where WareName like @wareName",
                //    new Microsoft.Data.SqlClient.SqlParameter("@wareName", likePattern)
                //    ).ContinueWith(t =>
                //    {
                //        Console.WriteLine($"销售总额：{t.Result}");
                //    });

            }
        }
    }
}
